namespace CISLib
{
    using System;
    using System.Data;
    using System.Data.SqlClient;

    public class ManageAdvice
    {
        public static DataSet GetAdviceInfoViaAreaId(Guid areaId)
        {
            string strSQL = "Select TB_Advice.*,TB_SysUser.loginName from TB_Advice left outer join TB_SysUser on TB_Advice.UserId = TB_SysUser.UserId where TB_Advice.AreaId=@AreaId ";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            return MyDB.DBReturnDataSet();
        }

        public static Advice GetAdviceInfoViaId(Guid adviceId)
        {
            SqlDataReader MyReader = null;
            Advice advice = new Advice();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string strSQL = "select * from TB_Advice where AdviceId = @AdviceId";
            MyDB.AddParameters(adviceId, "AdviceId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            if (MyReader.Read())
            {
                advice.AdviceId = MyReader.GetGuid(0);
                advice.AdviceTime = MyReader.GetDateTime(1);
                advice.AdviceTitle = MyReader.GetString(2);
                advice.AdviceContent = MyReader.GetString(3);
                advice.AdviceReContent = MyReader.GetString(4);
                advice.AreaId = MyReader.GetGuid(5);
                advice.UserId = MyReader.GetGuid(6);
            }
            MyDB.DBClose();
            return advice;
        }

        public static DataSet GetAdviceInfoViaUserId(Guid userId)
        {
            string strSQL = "Select TB_Advice.*,TB_SysUser.loginName from TB_Advice left outer join TB_SysUser on TB_Advice.UserId = TB_SysUser.UserId where TB_Advice.UserId=@UserId ";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(userId, "UserId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            return MyDB.DBReturnDataSet();
        }

        public static void InsertNewAdviceInfo(DateTime adviceTime, string advinceTitle, string advinceContent, string adviceReContent, Guid areaId, Guid userId)
        {
            Guid adviceId = Guid.NewGuid();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(adviceId, "AdviceId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(advinceTitle, "AdviceTitle", SqlDbType.VarChar);
            MyDB.AddParameters(advinceContent, "AdviceContent", SqlDbType.VarChar);
            MyDB.AddParameters(adviceReContent, "AdviceReContent", SqlDbType.VarChar);
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(userId, "UserId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(adviceTime, "AdviceTime", SqlDbType.DateTime);
            string strSQL = "Insert into TB_Advice (AdviceId,AdviceTitle,AdviceContent,AdviceReContent,AreaId,UserId,AdviceTime) values (@AdviceId,@AdviceTitle,@AdviceContent,@AdviceReContent,@AreaId,@UserId,@AdviceTime)";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void RemoveAdviceInfoViaId(Guid adviceId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(adviceId, "AdviceId", SqlDbType.UniqueIdentifier);
            string strSQL = "Delete From TB_Advice where AdviceId = @AdviceId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void UpdateAdviceInfo(Guid adviceId, string advinceTitle, string advinceContent, string adviceReContent, Guid areaId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(adviceId, "AdviceId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(advinceTitle, "AdviceTitle", SqlDbType.VarChar);
            MyDB.AddParameters(advinceContent, "AdviceContent", SqlDbType.VarChar);
            MyDB.AddParameters(adviceReContent, "AdviceReContent", SqlDbType.VarChar);
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            string strSQL = "Update TB_Advice set AdviceTitle=@AdviceTitle,AdviceContent=@AdviceContent,AdviceReContent=@AdviceReContent,AreaId=@AreaId where AdviceId=@AdviceId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }
    }
}

